﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using System.Text;

namespace HongLingJin
{
    public class VisitorInfoDAL
    {
        public VisitorInfo ToVisitorInfo(DataRow row)
        {
            VisitorInfo vi = new VisitorInfo();
            vi.Id = (string)row["id"];
            vi.File = (string)row["file"];
            vi.Name = (string)row["name"];
            vi.Visitee_name = (string)row["visitee_name"];
            vi.Identity_number = (string)row["identity_number"];
            vi.Visit_purpose = (string)row["visit_purpose"];
            vi.createDate = (Int64)row["createDate"];
            return vi;
        }
        public int Insert(VisitorInfo vi)
        {
            SQLiteHelper.CreateConnectionString();
            List<string> listParaName = new List<string>();
            List<SQLiteParameter> listPara = new List<SQLiteParameter>();
            IniList(vi, listParaName, listPara);
            string sql = "insert into v_visitor_info({0}) values({1});";
            sql = string.Format(sql, string.Join(",", listParaName), "@" + string.Join(",@", listParaName));
            return SQLiteHelper.ExecuteNonQuery(sql, listPara.ToArray());
        }

        public List<VisitorInfo> selectVisitorInfo(int start,int end,int selectIndex)
        {
            List<VisitorInfo> vis = new List<VisitorInfo>();
            DataTable dTable = new DataTable();
            if (selectIndex == 0)
            {
                dTable = SQLiteHelper.GetDataTable("select * from v_visitor_info where createDate>='" + start + "' and createDate<='" + end + "' order by createDate desc");
            }
            else
            {
                dTable = SQLiteHelper.GetDataTable("select * from v_visitor_info where createDate>='" + start + "' order by createDate desc");
            }
            
            for (int i = 0; i < dTable.Rows.Count; i++)
            {
                VisitorInfo vi = new VisitorInfo();
                vi = ToVisitorInfo(dTable.Rows[i]);
                vis.Add(vi);
            }
            return vis;
            
        }

        public int VisitorInfoCount(int start, int end, int selectIndex)
        {
            int count = 0;
            if (selectIndex == 0)
            {
                count = SQLiteHelper.GetDataCount("select count(id) from v_visitor_info where createDate>='" + start + "' and createDate<=" + end);
            }
            else
            {
                count = SQLiteHelper.GetDataCount("select count(id) from v_visitor_info where createDate>=" + start);
            }
            
            return count;
        }
        private void IniList(VisitorInfo vi, List<string> listParaName, List<SQLiteParameter> listPara)
        {
            if (vi.Id!= null)
            {
                listParaName.Add("id");
                listPara.Add(new SQLiteParameter("@Id", vi.Id));
            }

            if (vi.Card_no!= null)
            {
                listParaName.Add("card_no");
                listPara.Add(new SQLiteParameter("@Card_no", vi.Card_no));
            }

            if (vi.Company!= null)
            {
                listParaName.Add("company");
                listPara.Add(new SQLiteParameter("@Company", vi.Company));
            }

            if (vi.Identity_number!= null)
            {
                listParaName.Add("identity_number");
                listPara.Add(new SQLiteParameter("@Identity_number", vi.Identity_number));
            }

            if (vi.Name!= null)
            {
                listParaName.Add("name");
                listPara.Add(new SQLiteParameter("@Name", vi.Name));
            }

            if (vi.Phone!= null)
            {
                listParaName.Add("phone");
                listPara.Add(new SQLiteParameter("@Phone", vi.Phone));
            }

            if (vi.Visit_end_timestamp>=0)
            {
                listParaName.Add("visit_end_timestamp");
                listPara.Add(new SQLiteParameter("@Visit_end_timestamp", vi.Visit_end_timestamp));
            }

            if (vi.Visit_purpose!= null)
            {
                listParaName.Add("visit_purpose");
                listPara.Add(new SQLiteParameter("@Visit_purpose", vi.Visit_purpose));
            }

            if (vi.Visit_start_timestamp>=0)
            {
                listParaName.Add("visit_start_timestamp");
                listPara.Add(new SQLiteParameter("@Visit_start_timestamp", vi.Visit_start_timestamp));
            }

            if (vi.Visit_time_type != null)
            {
                listParaName.Add("visit_time_type");
                listPara.Add(new SQLiteParameter("@Visit_time_type", vi.Visit_time_type));
            }

            if (vi.Visitee_name != null)
            {
                listParaName.Add("visitee_name");
                listPara.Add(new SQLiteParameter("@Visitee_name", vi.Visitee_name));
            }

            if (vi.createDate >= 0)
            {
                listParaName.Add("createDate");
                listPara.Add(new SQLiteParameter("@createDate", vi.createDate));
            }
            if (vi.File != null)
            {
                listParaName.Add("file");
                listPara.Add(new SQLiteParameter("@File", vi.File));
            }
        }
    }
}
